package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import ModelLayer.City;

public class DBCity   {
	private Connection con = DBConnection.getInstance().getDBcon();
	
	public City getCityByCodeZip(int codeZip, boolean retriveAssociation) {
		String wClause = "codezip = '" + codeZip + "'";
		System.out.println("Search codeZip " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}
	
	//Singlewhere to select a city 	
		private City singleWhere(String wClause, boolean retrieveAssociation)
		{
			ResultSet results;
			City cityObj = new City();
			
		
				String query =  buildQuery(wClause);
				System.out.println(query);
				try{ // read the customer from the database
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					results = stmt.executeQuery(query);

					if( results.next() ){
						cityObj = buildCity(results);
						stmt.close();
					}		
					else{ //no customer was found
						cityObj = null;
					}
				}
				catch(Exception e){
					System.out.println("Query exception: "+e);
				}

			return cityObj;
		}
		
		//method to build the query for Customer
		private String buildQuery(String wClause)
		{
			String query="SELECT codezip, cityname FROM City";

			if (wClause.length()>0)
				query=query+" WHERE "+ wClause;

			return query;
		}
		
		//method to build a PrivateCustomer object
		private City buildCity(ResultSet results)
		{   
			City cityObj = new City();
			try{ // the columns from the table customer  are used
				cityObj.setCity((results.getString("cityname")));
				cityObj.setCodeZip(Integer.parseInt(results.getString("codezip")));
	
			}
			catch(Exception e)
			{
				System.out.println("error in building the city object");
			}
			return cityObj;
		}
}
